﻿using CaterModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CaterDal
{
    public partial class MemberInfoDal
    {
        public List<MemberInfo> GetList(Dictionary<string, string> dic)
        {
            string sql = "select mi.*,mti.mTitle as MTypeTitle from MemberInfo as mi inner join MemberTypeInfo as mti on mi.mTypeId=mti.mid where mi.MIsdelete=0";

            //拼接条件
            if(dic.Count>0)
            {
                foreach (var pair in dic)
                {
                    sql += " and " + pair.Key + " like '%" + pair.Value+"%'";
                }
            }

            DataTable dt = SqliteHelper.GetDataTable(sql);

            List<MemberInfo> list = new List<MemberInfo>();
            foreach (DataRow row in dt.Rows)
            {
                list.Add(new MemberInfo()
                {
                    MId = Convert.ToInt32(row["mid"]),
                    MTypeId = Convert.ToInt32(row["MTypeId"].ToString()),
                    MName = row["MName"].ToString(),
                    MPhone = row["MPhone"].ToString(),
                    MMoney = Convert.ToDecimal(row["MMoney"].ToString()),
                    MIsDelete = Convert.ToBoolean(row["MIsDelete"]),
                    MTypeTitle = row["MTypeTitle"].ToString()
                });
            }

            return list;
        }

        public int Insert(MemberInfo mi)
        {
            string sql = "insert into MemberInfo (MTypeId,MName,MPhone,MMoney,MIsDelete) values(@MTypeId,@MName,@MPhone,@MMoney,0)";
            SQLiteParameter[] ps = {
                new SQLiteParameter("@MTypeId",mi.MTypeId),
                 new SQLiteParameter("@MName",mi.MName),
                  new SQLiteParameter("@MPhone",mi.MPhone),
                 new SQLiteParameter("@MMoney",mi.MMoney),
            };

            return SqliteHelper.ExecuteNonQuery(sql, ps);
        }

        public int Update(MemberInfo mi)
        {

            List<SQLiteParameter> listPs = new List<SQLiteParameter>();
            string sql = "update MemberInfo set MTypeId=@MTypeId";
            sql += ",MName=@MName,MPhone=@MPhone,MMoney=@MMoney where  mid=@mid";

            listPs.Add(new SQLiteParameter("@MTypeId", mi.MTypeId));
            listPs.Add(new SQLiteParameter("@MName", mi.MName));
            listPs.Add(new SQLiteParameter("@MPhone", mi.MPhone));
            listPs.Add(new SQLiteParameter("@MMoney", mi.MMoney));
            listPs.Add(new SQLiteParameter("@mid", mi.MId));
            return SqliteHelper.ExecuteNonQuery(sql, listPs.ToArray());

        }

        public int Delete(int mid)
        {

            
            string sql = "update MemberInfo set MIsdelete=1 where  mid=@mid";

            SQLiteParameter p = new SQLiteParameter("@mid", mid);
          

            return SqliteHelper.ExecuteNonQuery(sql, p);

        }
    }
}
